const express = require("express");
const multer = require('multer');
const fs =require('fs');
const path = require('path');
const router = express.Router();
const pool = require("../pool.js");
const utils = require("../utils/index.js");
var upload = multer({ dest: 'uploads/' });
var xlsx = require('xlsx');
const {systemSettings} =require("../utils/menuString");
const cron = require('node-cron');
const dayjs = require('dayjs');
const CHANGE_TIME = '2024-06-05 10:00:00'; 
// const CHANGE_TIME = '2024-06-03 12:00:00'; 





/**
 * @swagger
 * components:
 *  securitySchemes:
 *   ApiKeyAuth:
 *     type: apiKey 
 *     in: header 
 *     name: Authorization
 */

  
/**
 * @swagger
 * /admin/login:
 *   post:
 *     tags:
 *       - 管理后台-用户相关
 *     summary: 用户登录
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               name:
 *                 type: string
 *                 description: The user's name.
 *                 example: test
 *               pwd:
 *                 type: string
 *                 description: The user's pwd.
 *                 example: 12345
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
//登录
router.post("/login", async (req, res) => {
    console.log(11,req.body);
    let sql = "SELECT id,admin FROM user WHERE name=? AND pwd=?", obj = req.body;
    console.log(obj.name, obj.pwd);
    pool.query(sql, [obj.name, obj.pwd], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        if (result.length == 0) return res.send(utils.returnData({code: -1, msg: "请输入正确的用户名和密码！",err,req}));
        let uid = result[0].id, admin = result[0].admin,name = obj.name;
        let token = utils.setToken({uid});
        res.send(utils.returnData({data: {uid, name, token, admin}}));
    });
});

//获取用户信息
router.post("/getUserInfo",async (req,res)=>{
    let user = await utils.getUserRole(req, res);
    let sql = `SELECT b.menu_bg AS menuBg,b.menu_sub_bg AS menuSubBg,b.menu_text AS menuText,b.menu_active_text AS menuActiveText,b.menu_sub_active_text AS menuSubActiveText,b.menu_hover_bg AS menuHoverBg FROM theme AS b WHERE user_id=?`;
    pool.query(sql,[user.user.id],(err,result)=>{
        if(err||result.length===0) return  res.send(utils.returnData({code:-1,err,req}));
        res.send(utils.returnData({data:{...user,theme:result[0]}}));
    })

})


function getRouter(req,res,sidebar=false){
    return new Promise(async (resolve, reject)=>{
        let sql = "SELECT id,parent_id AS parentId,path,hidden,redirect,always_show AS alwaysShow,name,layout,parent_view AS parentView,meta,component,sort,update_time AS updateTime,alone,role_key AS roleKey,menu_type AS menuType FROM router_menu ORDER BY sort ASC, update_time DESC";
        let userRole = await utils.getUserRole(req, res);
        if (userRole == -1) return res.send(utils.returnData({code: -1,req}));
        if (!userRole.userRole || userRole.userRole == null || userRole.userRole == "null") userRole.userRole = "";
        //角色权限
        let roles = userRole.userRole.split(",");
        pool.query(sql, (err, result) => {
            if (err) return res.send(utils.returnData({code: -1,err,req}));
            let list = [...result],routerArr=[];
            let filterAsyncRoutes = (lists, parentId, pathView = "") => {
                let resArr = [], obj = {};
                lists.map((t) => {
                    let objs = {...t};
                    try {objs.meta = JSON.parse(objs.meta);} catch (err) {objs.meta = {};}
                    objs.title = objs.meta.title || "---";
                    objs.pathView = t.path;
                    //按钮自动隐藏
                    if(objs.menuType==="F") objs.hidden=1;
                    //递归
                    if (objs.parentId == parentId) {
                        objs.path = pathView + objs.path;
                        obj = {...objs, children: filterAsyncRoutes(list, objs.id, objs.path)};
                        //菜单下有子级，单独拿出来
                        if(obj.menuType==="C"&&obj.children&&obj.children.length!=0){
                            routerArr.push(...obj.children)
                            sidebar&&delete obj.children;
                        }
                        //如果是总管理
                        if (userRole.user.admin == 1 || userRole.roleAdmin ) {resArr.push(obj);} else {
                            //只拿角色权限通过的
                            if (roles.some((role) => obj.id == role)) resArr.push(obj);
                        }
                    }
                });
                return resArr;
            };
            let routerMenu = filterAsyncRoutes(list, 0, "");
            //如果是独立的（一级）
            sidebar&&routerMenu.forEach(t=>{
                if(t.menuType==="C"&&(!t.children||t.children.length===0)){
                    t.layout=1;
                    t.children=[{...t, layout:0, alone:1, children:undefined,}]
                    t.path="/"+Math.random();
                }
            });
            resolve({routerMenu,routerArr})
        });

    })
}
//获取路由 侧边栏
router.post("/getRouter", async (req, res) => {
    let {routerMenu,routerArr}=await getRouter(req,res,true);
    function bianpinghua(list){
        let arr=[];
        list.map(t=>{
            if(t.children&&t.children.length) arr.push(...bianpinghua(t.children))
            arr.push({...t,layout:1,path:"/"+Math.random(),children: [{...t,layout:0, alone:1, children:undefined}],hidden:1});
        })
        return arr
    }
    routerArr=bianpinghua(routerArr);
    routerArr= routerArr.filter((obj, index, self) => index === self.findIndex((t) => (t.id === obj.id)));
    res.send(utils.returnData({data:{routerMenu:routerMenu.concat(routerArr)}}))
});
//菜单管理获取
router.post("/getRouterSystem", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.menus.menuQuery]});
    let {routerMenu}=await getRouter(req,res);
    res.send(utils.returnData({data:{routerMenu}}));
})
//添加菜单
router.post("/addMenu", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.menus.menuAdd]});
    let sql = "INSERT INTO router_menu(parent_id,path,hidden,name,layout,parent_view,meta,component,sort,alone,role_key,menu_type,update_time) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
        obj = req.body;
    let meta = {};
    await utils.existName({sql:"SELECT id FROM router_menu WHERE role_key=?",name:obj.roleKey,res,msg:"权限字符已存在！",req});
    await utils.existName({sql:"SELECT id FROM router_menu WHERE name=?",name:obj.name,res,msg:"页面名称已存在！！",req});
    meta.title = obj.title;
    meta.icon = obj.icon;
    meta.noCache = obj.noCache;
    pool.query(sql, [obj.parentId, obj.path, obj.hidden, obj.name, obj.parentId == 0 ? 1 : 0, obj.parentView, JSON.stringify(meta), obj.component, obj.sort, obj.alone, obj.roleKey, obj.menuType,new Date(),], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//修改菜单
router.post("/changeMenu", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.menus.menuUp]});
    let sql = "UPDATE  router_menu SET parent_id=?,path=?,hidden=?,name=?,layout=?,parent_view=?,meta=?,component=?,sort=?,alone=?,role_key=?,menu_type=?,update_time=? WHERE id=?",
        obj = req.body;
    let judgeUserNameRes = await utils.judgeUserName({sql:"SELECT role_key FROM router_menu WHERE  id=?",sqlName:"role_key",name:obj.roleKey,id:obj.id});
    if(judgeUserNameRes===1)await utils.existName({sql:"SELECT id FROM router_menu WHERE role_key=?",name:obj.roleKey,res,msg:"权限字符已存在！",req});
    let judgeUserNameRes2 = await utils.judgeUserName({sql:"SELECT name FROM router_menu WHERE  id=?",sqlName:"name",name:obj.name,id:obj.id});
    let meta = {};
    if(judgeUserNameRes2===1)await utils.existName({sql:"SELECT id FROM router_menu WHERE name=?",name:obj.name,res,msg:"页面名称已存在！",req});
    meta.title = obj.title;
    meta.icon = obj.icon;
    meta.noCache = obj.noCache;
    pool.query(sql, [obj.parentId, obj.path, obj.hidden, obj.name, obj.parentId == 0 ? 1 : 0, obj.parentView, JSON.stringify(meta), obj.component, obj.sort, obj.alone, obj.roleKey, obj.menuType,new Date(), obj.id,], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//删除菜单
router.post("/delMenu", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.menus.menuDelte]});
    let sql = "DELETE FROM router_menu WHERE id=?";
    let selectSql = "SELECT id FROM router_menu WHERE parent_id=?";
    let obj = req.body;
    pool.query(selectSql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        if (result.length != 0) return res.send(utils.returnData({code: -1, msg: "删除失败，请先删除子级",err,req}));
        pool.query(sql, [obj.id], (err2, result2) => {
            if (err2) return res.send(utils.returnData({code: -1,err,req}));
            res.send(utils.returnData({data: result}));
        });
    });
});
//查询角色
router.post("/getRoles", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.role.roleQuery]});
    let obj=req.body;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    let {total}=await utils.getSum({name:"roles",where:`WHERE name LIKE "%${obj.name||''}%"`,res,req});
    let sql = `SELECT id,name,roles,checked_roles AS checkedRoles,role_key AS roleKey,create_time AS createTime FROM roles WHERE name LIKE "%${obj.name||''}%" LIMIT ?,?`;
    pool.query(sql,[page,size] ,(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});
//查询角色全部
router.post("/getRolesAll", async (req, res) => {
    let sql = `SELECT id,name,roles,checked_roles AS checkedRoles,role_key AS roleKey FROM roles`;
    pool.query(sql,(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//添加角色
router.post("/addRoles", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.role.roleAdd]});
    let sql = "INSERT INTO roles(name,roles,checked_roles,role_key,create_time) VALUES (?,?,?,?,?)", obj = req.body;
    await utils.existName({sql:"SELECT id FROM roles WHERE role_key=?",name:obj.roleKey,res,msg:"权限字符已存在！",req});
    // console.log(obj);
    pool.query(sql, [obj.name, obj.roles, obj.checkedRoles,obj.roleKey,new Date()], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//修改角色
router.post("/upRoles", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.role.roleUp]});
    let sql = "UPDATE  roles SET roles=?,name=?,checked_roles=?,role_key=? WHERE id=?", obj = req.body;
    //总管理不能操作
    await utils.upAdminRole({req,res,id:obj.id});
    let judgeUserNameRes = await utils.judgeUserName({sql:"SELECT role_key FROM roles WHERE  id=?",sqlName:"role_key",name:obj.roleKey,id:obj.id});
    if(judgeUserNameRes===1)await utils.existName({sql:"SELECT id FROM roles WHERE role_key=?",name:obj.roleKey,res,msg:"权限字符已存在！",req});
    pool.query(sql, [obj.roles, obj.name, obj.checkedRoles, obj.roleKey,obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//删除角色
router.post("/delRoles", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.role.roleDelte]});
    let sql = "DELETE FROM roles WHERE id=?", obj = req.body;
    //总管理不能操作
    await utils.upAdminRole({req,res,id:obj.id});
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});

//添加用户
router.post("/addUser", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.user.userAdd]});
    let sql = "INSERT INTO user(name,status,roles_id,remark,pwd,more_id,create_time) VALUES (?,?,?,?,?,?,?)", obj = req.body;
    await utils.existName({sql: "SELECT id FROM user WHERE  name=?", name: obj.name,res,msg:"用户名已被使用！",req});
    pool.query(sql, [obj.name, obj.status,obj.rolesId, obj.remark, obj.pwd, obj.moreId,new Date()], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err}));
        let themeSql="INSERT INTO theme(user_id,menu_bg,menu_sub_bg,menu_text,menu_active_text,menu_sub_active_text,menu_hover_bg) VALUES (?,?,?,?,?,?,?)";
        pool.query(themeSql,[result.insertId,"#304156","#304156","#bfcad5","#409eff","#fff","#001528"],(themeErr,themeRes)=>{
            if (themeErr) return res.send(utils.returnData({code: -1,err:themeErr,req}));
            res.send(utils.returnData({data: result}));
        })

    });
});

//查询用户
router.post("/getUser", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.user.userQuery]});
    let obj=req.body;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    let {total}=await utils.getSum({name:"user",where:`WHERE name LIKE "%${obj.name||''}%"`,res,req});
    let sql = `SELECT a.id AS id,name,status,roles_id AS rolesId,remark,admin,more_id AS moreId,a.create_time AS createTime FROM user AS a  WHERE name LIKE "%${obj.name||''}%" LIMIT ?,?`;
    pool.query(sql, [page,size],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});


//修改主题
router.post("/upTheme", async (req, res) => {
    let sql = "UPDATE  theme SET menu_bg=?,menu_sub_bg=?,menu_text=?,menu_active_text=?,menu_sub_active_text=?,menu_hover_bg=? WHERE user_id=?", obj = req.body;
    pool.query(sql, [obj.menuBg,obj.menuSubBg,obj.menuText,obj.menuActiveText,obj.menuSubActiveText,obj.menuHoverBg,obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});



//修改用户
router.post("/upUser", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.user.userUp]});
    let sql = "UPDATE  user SET name=?,status=?,roles_id=?,remark=?,more_id=? WHERE id=?", obj = req.body;
    //总管理不能操作
    await utils.upAdmin({req,res,id:obj.id});
    let judgeUserNameRes = await utils.judgeUserName({sql:"SELECT name FROM user WHERE  id=?",name:obj.name,id:obj.id});
    if (judgeUserNameRes === 1) await utils.existName({sql: "SELECT id FROM user WHERE  name=?", name: obj.name,res,msg:"用户名已被使用！",req});
    pool.query(sql, [obj.name, obj.status,obj.rolesId, obj.remark, obj.moreId, obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});

//修改用户密码
router.post("/upUserPwd", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.user.userPwd]});
    let sql = "UPDATE  user SET pwd=? WHERE id=?", obj = req.body;
    let getUserIdRes=await utils.getUserId({id:obj.id,req,res});
    if(getUserIdRes.admin===1){
        let user=await utils.getUserInfo(req,res);
        if(user.admin!==1) return res.send(utils.returnData({code: -1,msg:"总管理密码只能总管理账号修改！",req}));
    }
    pool.query(sql, [obj.pwd,obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});

//删除用户
router.post("/delUser", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.user.userDelte]});
    let obj = req.body;
    //总管理不能操作
    await utils.upAdmin({req,res,id:obj.id});
    let user = await utils.getUserInfo(req, res);
    if (obj.id == user.id) return res.send(utils.returnData({code: -1, msg: "无法删除正在使用中的用户~",req}));
    let sql = "DELETE FROM user WHERE id=?";
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err:err,req}));
        res.send(utils.returnData({data: result}));
    });
});




























/**
 * @swagger
 * /admin/addProduct:
 *   post:
 *     tags:
 *       - 管理后台-产品管理
 *     summary: 新建产品
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               source:
 *                 type: string
 *                 description: 产品归属
 *                 example: 来伊份
 *               skuId:
 *                 type: string
 *                 description: 产品ID
 *                 example: 
 *               skuCode:
 *                 type: string
 *                 description: 产品编码
 *                 example: 
 *               skuName:
 *                 type: string
 *                 description: 产品名称
 *                 example: 
 *               imgUrl:
 *                 type: string
 *                 description: 产品封面
 *                 example: 
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */

//新建产品
router.post("/addProduct", async (req, res) => {
    let obj=req.body;
    console.log(99889,obj); 
    let sql = `INSERT INTO product (scode,discount_type,discount,iscoupon,sku_name,type, img_url,img_details,description,price,orinprice,inventory,state,detailJson,update_time,create_time) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),now());`;
    pool.query(sql, [obj.scode,obj.discountType,obj.discount,obj.isCoupon,obj.skuName,obj.type.join(','),obj.imgUrl,obj.imgDetails.join(','),obj.description,obj.price,obj.orinprice,obj.inventory,obj.state,obj.detailJson],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});

/**
 * @swagger
 * /admin/upProduct:
 *   post:
 *     tags:
 *       - 管理后台-产品管理
 *     summary: 更新产品
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               source:
 *                 type: string
 *                 description: 产品归属
 *                 example: 来伊份
 *               skuId:
 *                 type: string
 *                 description: 产品ID
 *                 example: 
 *               skuCode:
 *                 type: string
 *                 description: 产品编码
 *                 example: 
 *               skuName:
 *                 type: string
 *                 description: 产品名称
 *                 example: 
 *               imgUrl:
 *                 type: string
 *                 description: 产品封面
 *                 example: 
 *               id:
 *                 type: number
 *                 description: id
 *                 example: 
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */

//更新产品
router.post("/upProduct", async (req, res) => {
    let obj=req.body;
    console.log(7878,obj); 
    let sql = `UPDATE  product  SET  scode=?,discount_type=?,discount=?,iscoupon=?,sku_name=?,type=?, img_url=?,description=?,img_details=?,price=?,orinprice=?,inventory=?,state=?,detailJson=?,update_time=?,ydurl=?  where id=?;`;
    pool.query(sql, [obj.scode,obj.discountType,obj.discount,obj.isCoupon,obj.skuName,obj.type.join(','),obj.imgUrl,obj.description,obj.imgDetails.join(','),obj.price,obj.orinprice,obj.inventory,obj.state,obj.detailJson,new Date(),obj.ydurl,obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});

/**
 * @swagger
 * /admin/getProduct:
 *   post:
 *     tags:
 *       - 管理后台-产品管理
 *     summary: 产品列表
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               page:
 *                 type: string
 *                 description: 
 *                 example: 1
 *               size:
 *                 type: string
 *                 description:  
 *                 example: 10
 *               skuName:
 *                 type: string
 *                 description: 
 *                 example: 
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */

//查询产品列表
router.post("/getProduct", async (req, res) => {
    let obj=req.body;
    console.log(99889,obj); 
    let sql = `SELECT id,ydurl,scode, discount_type as discountType,discount,iscoupon,source, sku_id as skuId, sku_code as skuCode, sku_name as skuName, img_url as imgUrl,img_details as imgDetails,description,price,orinprice,inventory,type,state,update_time as updateTime, create_time as createTime,detailJson  FROM product  WHERE  sku_id = '' and  sku_name LIKE "%${obj.skuName||''}%"  and iscoupon='${obj.isCoupon}'    ORDER BY update_time DESC LIMIT ?,?`;
    let {total}=await utils.getSum({name:"product",where:`WHERE sku_id = '' and sku_name LIKE "%${obj.skuName||''}%" and iscoupon='${obj.isCoupon}'`,res,req}) ;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    pool.query(sql, [page,size],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});
router.post("/getProductItem", async (req, res) => {
    let obj=req.body;
    console.log(99889,obj); 
    let sql = `SELECT id,scode from product`;
    pool.query(sql, [],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/getProductSelect", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT id,sku_name as skuName,img_url as imgUrl from product where  state=1 and discount_type is null`;
    pool.query(sql, [],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});




router.post("/delProduct", async (req, res) => {
    let sql = "DELETE FROM product WHERE id=?", obj = req.body;
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});


//联系上下文生成四个赠品的增删查改getGift,upGift,addGift,delGift,赠品的字段如下：id，商品id product_id,商品名称product_name,商品图片product_url,gift_level赠品等级






/**
 * @swagger
 * /admin/getOrder:
 *   post:
 *     tags:
 *       - 管理后台-订单管理
 *     summary: 订单列表
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */



//订单信息
router.post("/getOrder", async (req, res) => {
    console.log(999,CHANGE_TIME);
    let obj=req.body;
    let sql='';
    let where='';
    let trackIngType=obj.trackIngType;
    let receiveTime=obj.receiveTime;
    let ifStr="";
    let ifwhereStr="";
    let receiveTimeStr="";
    let receiveTimeStrWhere="";
    console.log(12345678,trackIngType);
    console.log(12345678,receiveTime);
    if(trackIngType==='0'){
        ifStr=" and  a.tracking_number = ''  ";
        ifwhereStr=" and  tracking_number  = ''  ";
    }
    if(trackIngType==='1'){
        console.log(9999);
        ifStr=" and  a.tracking_number != ''  ";
        ifwhereStr=" and  tracking_number  != ''  ";
    }
    if(receiveTime!=''&& receiveTime!=null && receiveTime.length!=0){
        console.log(8888);
        let reSTime=receiveTime[0];
        let reeTime=receiveTime[1];
        console.log(reSTime);
        console.log(reeTime);
        receiveTimeStr=`and a.receive_time BETWEEN '${reSTime}' and  '${reeTime}'`;
        receiveTimeStrWhere=`and receive_time BETWEEN '${reSTime}' and  '${reeTime}'`;
    }
    if(obj.createTime=='' || obj.createTime==null ||  obj.createTime.length==0){
        console.log(12133);
        sql= `SELECT a.id,CASE  WHEN a.create_time < '${CHANGE_TIME}'  THEN TRUE ELSE FALSE END AS is_his,a.massage_status as massageStatus,a.msgNum as msgNum, a.source,a.order_serial as orderSerial, a.order_id as orderId, a.phone, a.receive_time AS receiveTime, a.ext_code as extCode, a.quantity AS quantity, a.rights_id as rightsid, a.price, a.sku_id as skuId, a.sku_code as skuCode, a.sku_name AS skuName,  a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.created_at,a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address FROM t_order a       WHERE (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")  and  a.sku_code like  "%lyf0%"  and a.state LIKE "%${obj.state||''}%"   `+ifStr+`  `+receiveTimeStr+`     ORDER BY  a.receive_time DESC,a.create_time desc,a.tracking_number LIMIT ?,?`;
        where=`WHERE (phone LIKE "%${obj.keyword||''}%" or order_id LIKE "%${obj.keyword||''}%")    and  sku_code like  "%lyf0%"   and state LIKE "%${obj.state||''}%"  `+ifwhereStr  +receiveTimeStrWhere;
    }else{
        let startTime=obj.createTime[0];
        let endTime=obj.createTime[1];
        console.log(startTime);
        console.log(endTime);
        sql=`SELECT a.id,CASE WHEN a.create_time < '${CHANGE_TIME}'  THEN TRUE ELSE FALSE END AS is_his,a.massage_status as massageStatus,a.msgNum as msgNum,a.source, a.order_id as orderId,a.order_serial as orderSerial, a.phone, a.receive_time AS receiveTime, a.ext_code as extCode, a.quantity AS quantity, a.rights_id as rightsid, a.price, a.sku_id as skuId, a.sku_code as skuCode, a.sku_name AS skuName,  a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.created_at,a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address  FROM t_order a     WHERE    a.create_time BETWEEN '${startTime}' and  '${endTime}'    and  a.sku_code like  "%lyf0%"   and  (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")   and a.state LIKE "%${obj.state||''}%"     `+ifStr+`    `+receiveTimeStr+`   ORDER BY a.receive_time DESC,a.create_time desc,a.tracking_number  LIMIT ?,?`;
        where=`WHERE  create_time BETWEEN '${startTime}' and  '${endTime}'  and  sku_code like  "%lyf0%"   and    (phone LIKE "%${obj.keyword||''}%" or order_id LIKE "%${obj.keyword||''}%")   and state LIKE "%${obj.state||''}%"   `+ifwhereStr  +receiveTimeStrWhere;
    }
    console.log(123,where);
    console.log(123,sql);
    let {total}=await utils.getSum({name:"t_order",where:where,res,req}) ;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    pool.query(sql, [page,size],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});
router.post("/getAllOrder", async (req, res) => {
    req.setTimeout(20*1000);
    let obj=req.body;
    console.log(12345,obj);
    let sql='';
    let trackIngType=obj.trackIngType;
    let receiveTime=obj.receiveTime;
    let receiveTimeStr="";
    let ifStr="";
    if(trackIngType==='0'){
        ifStr=" and  a.tracking_number = ''  ";
    }
    if(trackIngType==='1'){
        console.log(9999);
        ifStr=" and  a.tracking_number != ''  ";
    }
    if(receiveTime!=''&& receiveTime!=null && receiveTime.length!=0){
        console.log(8888);
        let reSTime=receiveTime[0];
        let reeTime=receiveTime[1];
        console.log(reSTime);
        console.log(reeTime);
        receiveTimeStr=`and a.receive_time BETWEEN '${reSTime}' and  '${reeTime}'`;
    }
    if(obj.createTime=='' || obj.createTime==null ||  obj.createTime.length==0){
        sql= `SELECT a.id, a.source,a.order_serial as orderSerial, a.order_id as orderId, a.phone, a.receive_time AS receiveTime, a.ext_code as extCode, a.quantity AS quantity, a.rights_id as rightsid, a.price, a.sku_id as skuId, a.sku_code as skuCode, a.sku_name AS skuName,  a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.created_at,a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address FROM t_order a  WHERE (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")   and  a.sku_code like  "%lyf0%"  and a.state LIKE "%${obj.state||''}%"   `+ifStr+`  `+receiveTimeStr+`    ORDER BY a.receive_time DESC,a.create_time desc,a.tracking_number  `;
    }else{
        let startTime=obj.createTime[0];
        let endTime=obj.createTime[1];
        console.log(startTime);
        console.log(endTime);
        sql=`SELECT a.id, a.source, a.order_id as orderId,a.order_serial as orderSerial, a.phone, a.receive_time AS receiveTime, a.ext_code as extCode, a.quantity AS quantity, a.rights_id as rightsid, a.price, a.sku_id as skuId, a.sku_code as skuCode, a.sku_name AS skuName,  a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.created_at,a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address  FROM t_order a    WHERE    a.create_time BETWEEN '${startTime}' and  '${endTime}'  and  a.sku_code like  "%lyf0%"  and  (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")   and a.state LIKE "%${obj.state||''}%"     `+ifStr+`   `+receiveTimeStr+`    ORDER BY a.receive_time DESC,a.create_time desc,a.tracking_number `;
    }
    console.log(123456,sql);
    pool.query(sql, [],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});


//订单信息
router.post("/getNewOrder", async (req, res) => {
    console.log(999,CHANGE_TIME);
    let obj=req.body;
    let sql='';
    let where='';
    let trackIngType=obj.trackIngType;
    let receiveTime=obj.receiveTime;
    let ifStr="";
    let ifwhereStr="";
    let receiveTimeStr="";
    let receiveTimeStrWhere="";
    console.log(12345678,trackIngType);
    console.log(12345678,receiveTime);
    if(trackIngType==='0'){
        ifStr=" and  a.tracking_number = ''  ";
        ifwhereStr=" and  tracking_number  = ''  ";
    }
    if(trackIngType==='1'){
        console.log(9999);
        ifStr=" and  a.tracking_number != ''  ";
        ifwhereStr=" and  tracking_number  != ''  ";
    }
    if(receiveTime!=''&& receiveTime!=null && receiveTime.length!=0){
        console.log(8888);
        let reSTime=receiveTime[0];
        let reeTime=receiveTime[1];
        console.log(reSTime);
        console.log(reeTime);
        receiveTimeStr=`and a.pay_time BETWEEN '${reSTime}' and  '${reeTime}'`;
        receiveTimeStrWhere=`and pay_time BETWEEN '${reSTime}' and  '${reeTime}'`;
    }
    if(obj.createTime=='' || obj.createTime==null ||  obj.createTime.length==0){
        console.log(12133);
        sql= `SELECT a.id,CASE  WHEN a.create_time < '${CHANGE_TIME}'  THEN TRUE ELSE FALSE END AS is_his,a.massage_status as massageStatus,a.msgNum as msgNum, a.source, a.order_id as orderId, 
        a.phone, pay_time as payTime,	total_price as totalPrice,	total_discount as totalDiscount,	total_fee as totalFee,	coupon_id as couponId,goods_json as goodsJson,  
        a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address FROM t_new_order a       WHERE    (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")    and a.state in (1,2,3)   `+ifStr+`  `+receiveTimeStr+`     ORDER BY  a.pay_time DESC,a.create_time desc,a.tracking_number LIMIT ?,?`;
        where=`WHERE (phone LIKE "%${obj.keyword||''}%" or order_id LIKE "%${obj.keyword||''}%")     and state in (1,2,3)   `+ifwhereStr  +receiveTimeStrWhere;
    }else{
        let startTime=obj.createTime[0];
        let endTime=obj.createTime[1];
        console.log(startTime);
        console.log(endTime);
        sql=`SELECT a.id,CASE WHEN a.create_time < '${CHANGE_TIME}'  THEN TRUE ELSE FALSE END AS is_his,a.massage_status as massageStatus,a.msgNum as msgNum,a.source, a.order_id as orderId,
        a.phone, pay_time as payTime,	total_price as totalPrice,	total_discount as totalDiscount,	total_fee as totalFee,	coupon_id as couponId,goods_json as goodsJson, 
        a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address  FROM t_new_order a     WHERE    a.create_time BETWEEN '${startTime}' and  '${endTime}'      and  (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")   and a.state in (1,2,3)    `+ifStr+`    `+receiveTimeStr+`   ORDER BY a.pay_time DESC,a.create_time desc,a.tracking_number  LIMIT ?,?`;
        where=`WHERE  create_time BETWEEN '${startTime}' and  '${endTime}'    and    (phone LIKE "%${obj.keyword||''}%" or order_id LIKE "%${obj.keyword||''}%")   and state in (1,2,3)  `+ifwhereStr  +receiveTimeStrWhere;
    }
    console.log(123,where);
    console.log(123,sql);
    let {total}=await utils.getSum({name:"t_new_order",where:where,res,req}) ;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    pool.query(sql, [page,size],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});
router.post("/getAllNewOrder", async (req, res) => {
    req.setTimeout(20*1000);
    let obj=req.body;
    console.log(12345,obj);
    let sql='';
    let trackIngType=obj.trackIngType;
    let receiveTime=obj.receiveTime;
    let receiveTimeStr="";
    let ifStr="";
    if(trackIngType==='0'){
        ifStr=" and  a.tracking_number = ''  ";
    }
    if(trackIngType==='1'){
        console.log(9999);
        ifStr=" and  a.tracking_number != ''  ";
    }
    if(receiveTime!=''&& receiveTime!=null && receiveTime.length!=0){
        console.log(8888);
        let reSTime=receiveTime[0];
        let reeTime=receiveTime[1];
        console.log(reSTime);
        console.log(reeTime);
        receiveTimeStr=`and a.receive_time BETWEEN '${reSTime}' and  '${reeTime}'`;
    }
    if(obj.createTime=='' || obj.createTime==null ||  obj.createTime.length==0){
        sql= `SELECT a.id,a.massage_status as massageStatus,a.msgNum as msgNum, a.source, a.order_id as orderId, 
        a.phone, pay_time as payTime,	total_price as totalPrice,	total_discount as totalDiscount,	total_fee as totalFee,	coupon_id as couponId,goods_json as goodsJson,  
        a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address FROM t_new_order a       WHERE    (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")    and a.state in (1,2,3)   `+ifStr+`  `+receiveTimeStr+`     ORDER BY  a.pay_time DESC,a.create_time desc,a.tracking_number `;
    }else{
        let startTime=obj.createTime[0];
        let endTime=obj.createTime[1];
        console.log(startTime);
        console.log(endTime);
        sql=`SELECT a.id,a.massage_status as massageStatus,a.msgNum as msgNum,a.source, a.order_id as orderId,
        a.phone, pay_time as payTime,	total_price as totalPrice,	total_discount as totalDiscount,	total_fee as totalFee,	coupon_id as couponId,goods_json as goodsJson, 
        a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address  FROM t_new_order a     WHERE    a.create_time BETWEEN '${startTime}' and  '${endTime}'      and  (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")   and a.state in (1,2,3)     `+ifStr+`    `+receiveTimeStr+`   ORDER BY a.pay_time DESC,a.create_time desc,a.tracking_number`;
    }
    console.log(123456,sql);
    pool.query(sql, [],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});


/**
 * @swagger
 * /admin/getOrder:
 *   post:
 *     tags:
 *       - 管理后台-订单管理
 *     summary: 导入订单
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
router.post("/excleTrackingNumber", async (req, res) => {
    let obj=req.body;
    if(obj.length==0){
        res.send(utils.returnData({data: {}}));
    }else{
        var arry=[];
        for(let i=0;i<obj.length;i++){
            let  bodyJson = obj[i];
            console.log(888,bodyJson);
            let orderId=bodyJson.orderId;
            let trackingNumber=bodyJson.trackingNumber;
            let expressCompany=bodyJson.expressCompany;
            console.log("orderId-单号：",orderId+"-"+trackingNumber+"-"+expressCompany)
            let {total}=await utils.getSum({name:"t_order",where:`WHERE order_id="${orderId}"`,res,req});
            console.log(123,total);
            if(total==0){
                arry.push(Number(i+1));
            }else{
                let sql = `UPDATE  t_order SET    express_company=?,tracking_number=?,update_time=?,state=?   WHERE order_id=?`;
                pool.query(sql,[expressCompany,trackingNumber,new Date(),2,orderId],(err, result) => {
                    if (err) return res.send(utils.returnData({code: -1,err,req}));
                    // res.send(utils.returnData({data: result}));
                    //根据订单号查询用户的openid和商品购买信息
                    let sql2=`SELECT a.sku_name as skuName,b.open_id as openid  FROM t_order  a  left  join  t_user b on a.phone=b.phone where a.order_id=?`;
                    pool.query(sql2,[orderId],(err2, result2) => {
                        if (err2) return res.send(utils.returnData({code: -1,err2,req}));
                        console.log(result2[0]);
                        utils.startToast(result2[0].openid,expressCompany,trackingNumber,result2[0].skuName)
                    })
                });
            }
        }
        if(arry.length==0){
            res.send(utils.returnData({data: {}}));
        }else{
            res.send(utils.returnData({code: 250,data: {},msg:'第'+arry.join(',')+'行订单号不存在,对应行未更新成功！'}));
        }

        
    }
});


router.post("/excleTrackingNumberNew", async (req, res) => {
    let obj=req.body;
    if(obj.length==0){
        res.send(utils.returnData({data: {}}));
    }else{
        var arry=[];
        for(let i=0;i<obj.length;i++){
            let  bodyJson = obj[i];
            console.log(888,bodyJson);
            let orderId=bodyJson.orderId;
            let trackingNumber=bodyJson.trackingNumber;
            let expressCompany=bodyJson.expressCompany;
            console.log("orderId-单号：",orderId+"-"+trackingNumber+"-"+expressCompany)
            let {total}=await utils.getSum({name:"t_new_order",where:`WHERE order_id="${orderId}"`,res,req});
            console.log(123,total);
            if(total==0){
                arry.push(Number(i+1));
            }else{
                let sql = `UPDATE  t_new_order SET    express_company=?,tracking_number=?,update_time=?,state=?   WHERE order_id=?`;
                pool.query(sql,[expressCompany,trackingNumber,new Date(),2,orderId],(err, result) => {
                    if (err) return res.send(utils.returnData({code: -1,err,req}));
                    res.send(utils.returnData({data: result}));
                    //根据订单号查询用户的openid和商品购买信息
                    // let sql2=`SELECT a.sku_name as skuName,b.open_id as openid  FROM t_order  a  left  join  t_user b on a.phone=b.phone where a.order_id=?`;
                    // pool.query(sql2,[orderId],(err2, result2) => {
                    //     if (err2) return res.send(utils.returnData({code: -1,err2,req}));
                    //     console.log(result2[0]);
                    //     utils.startToast(result2[0].openid,expressCompany,trackingNumber,result2[0].skuName)
                    // })
                });
            }
        }
        if(arry.length==0){
            res.send(utils.returnData({data: {}}));
        }else{
            res.send(utils.returnData({code: 250,data: {},msg:'第'+arry.join(',')+'行订单号不存在,对应行未更新成功！'}));
        }

        
    }
});

//修改订单状态
  router.post("/updateMassageState", async (req, res) => {
    let orderId=req.body.orderId;
    let sql = `UPDATE  t_order SET massage_status=?,update_time=?   WHERE order_id=?`;
    pool.query(sql,[200,new Date(),orderId],(err, result) => {
        console.log(err);
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});



//删除订单
// router.post("/delOrder", async (req, res) => {
//     let sql = "DELETE FROM orders WHERE id=?", obj = req.body;
//     pool.query(sql, [obj.id], (err, result) => {
//         if (err) return res.send(utils.returnData({code: -1,err,req}));
//         res.send(utils.returnData({data: result}));
//     });
// });


/**
 * @swagger
 * /admin/getOrder:
 *   post:
 *     tags:
 *       - 管理后台-订单管理
 *     summary: 添加单号
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
router.post("/upOrder", async (req, res) => {
    let obj=req.body;
    let id=obj.id;
    let trackingNumber=obj.trackingNumber;
    console.log("id-单号：",id+"-"+trackingNumber)
    let sql = `UPDATE  t_order SET tracking_number=?,state=?,update_time=?   WHERE id=?`;
    pool.query(sql,[trackingNumber,2,new Date(),id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: {}}));
        //根据订单号查询用户的openid和商品购买信息
        // let sql2=`SELECT a.sku_name as skuName,b.open_id as openid  FROM t_order  a  left  join  t_user b on a.phone=b.phone where a.id=?`;
        // pool.query(sql2,[id],(err2, result2) => {
        //     if (err2) return res.send(utils.returnData({code: -1,err2,req}));
        //     console.log(result2[0]);
        //     utils.startToast(result2[0].openid,"邮政",trackingNumber,result2[0].skuName)
        // })
    });
});


router.post("/upNewOrder", async (req, res) => {
    let obj=req.body;
    let id=obj.id;
    let trackingNumber=obj.trackingNumber;
    console.log("id-单号：",id+"-"+trackingNumber)
    let sql = `UPDATE  t_new_order SET tracking_number=?,state=?,update_time=?   WHERE id=?`;
    pool.query(sql,[trackingNumber,2,new Date(),id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: {}}));
    });
});









//单文件上传
router.post('/upload', multer({
    dest:'public'
  }).array('file',10),(req,res)=>{
    console.log(888,req.files)
    const files = req.files;
    const fileList = [];
    if(files.length>=1){
        for(var i in files){
            var file = files[i];
            fs.renameSync(file.path,`public/${file.originalname}`);
            file.path = ` public/${file.originalname}`;
            fileList.push(file)
          }
          res.send(fileList[0].originalname)
    }else{
        res.send(null)
    }
    
    
  }
  );
  //多文件
  router.post('/uploads', multer({
    dest:'public'
  }).array('file',10),(req,res)=>{
    console.log(666,req.files)
    const files = req.files;
    const fileList = [];
    if(files.length>=1){
        for(var i in files){
            var file = files[i];
            fs.renameSync(file.path,`public/${file.originalname}`);
            file.path = ` public/${file.originalname}`;
            fileList.push(file)
          }
          let name="";
          fileList.map((item, index) => {
              if(index==0){
                  name=name+item.originalname
              }else{
                  name=name+','+item.originalname
              }
          });
          res.send(name)
    }else{
        res.send(null)
    }
    
  }
  );




  //测试
  router.get('/test',(req,res) => {
    const query = req.query
    res.send({
        msg:'测试专用',
        data:{}
    })
})




//创建一个每分钟执行一次的定时任务
cron.schedule('* * * * *',async () => {
    let sqlh ="update t_order  set  msgNum=3,massage_status=0   where  massage_status=250 and msgNum>0 and state=0 and TIMESTAMPDIFF(HOUR, update_time, NOW()) >= 2";
    pool.query(sqlh, [],(errh, resulth) => {
        if (errh) return console.log(errh);
    });
    let datetime=utils.getDateTime();
    let date=utils.getDate();
    let sql1 = `update t_order set state=3 where create_time < DATE_SUB(CURDATE(), INTERVAL 30 DAY) and state=0 and (sku_code='lyf01' or sku_code='lyf02')`;
    let sql2 = `update t_order set state=3 where create_time < DATE_SUB(CURDATE(), INTERVAL 90 DAY) and state=0 and sku_code='lyf03'`;
    pool.query(sql1,[],(err, result) => {
        if (err) console.log(err);
        pool.query(sql2,[],(err2, result2) => {if (err2) console.log(err2);});
    });
    var page=1;
  var bigData=[];
  while (true) {
     
    let result =await utils.getLaiyifenOrder(date,page);
    bigData= bigData.concat(result);
    // 检查条件是否满足，如果满足则退出循环
    if (result.length<50) {
      console.log('条件满足，退出循环');
      break;
    }
    page++;
  }  
  for(let i=0;i<bigData.length;i++){
    let  bodyJson = bigData[i];
    let phone=bodyJson.serverNum;
    //查询用户
    let userId=await utils.getTUserIdByCorn({phone:phone});

    //查询产品Id
    let skuId=bodyJson.skuId;
    let skuCode=bodyJson.skuCode;
    let skuName=bodyJson.skuName;
    let msgName=null;
    if(skuCode=='lyf01'){
        skuName="来伊份零食畅享盲盒月卡";
        msgName="零食畅享盲盒";
    }
    if(skuCode=='lyf02'){
        skuName="来伊份零食精选盲盒月卡";
        msgName="零食精选盲盒";
    }
    if(skuCode=='lyf03'){
        skuName="来伊份零食十全大礼包";
        msgName="十全大礼包";
    }
    let productId=await utils.getProductIdByCorn({skuId,skuCode,skuName});
    let orderId=bodyJson.orderItemId;
    let extCode=bodyJson.extCode;
    let quantity=bodyJson.quantity;
    let rightsId=bodyJson.rightsId;
    let price=bodyJson.price;
    let detailAddress=bodyJson.recipientAddress;

    let recipientNumber=bodyJson.recipientNumber;
    let receiveTime;
    if(bodyJson.receiveTime==null ||  bodyJson.receiveTime==''){
      receiveTime=null;
    }else{
      receiveTime= dayjs(Number(bodyJson.receiveTime)).format('YYYY-MM-DD HH:mm:ss');
    }
    let trackingNumber=bodyJson.trackingNumber;
    let state=0;
    if(recipientNumber!='' && recipientNumber!=null){
      trackingNumber!=''  && trackingNumber!=null?state=2:state=1
    }
    let createTime = dayjs(Number(bodyJson.createTime)).format('YYYY-MM-DD HH:mm:ss')
    let order_serial='';
    for (let i = 0; i < 12; i++) {
      order_serial += Math.floor(Math.random() * 10); // 生成0到9之间的随机数
    }
    let orderRes=await utils.getSumByCorn({name:"t_order",where:`WHERE order_id="${orderId}"`});
    if(state==0){
      console.log("插入状态为0的订单");
      //未领取
      //插入订单-判断订单是否存在，不存在就新增，存在不做操作
      if(orderRes.total==0){
        let createTimestamp = new Date(createTime).getTime();
        let targetTimestamp = new Date(CHANGE_TIME).getTime();
        let timeDiff = createTimestamp - targetTimestamp;
        let msgId='';
        if(timeDiff > 0  && msgName!=null){
          //非历史订单
          let mesData=await utils.sedHttpMessage(phone,msgName);
          let mesData2=await utils.sedHttpSpringMessage(phone);
          msgId=mesData.msgId;
        }
        let sql ="INSERT INTO  t_order(source, order_id, phone, receive_time, ext_code, quantity,rights_id, price, product_id, sku_id, sku_code, sku_name, user_id, user_consignee_id, express_company, tracking_number, state, sms_num, create_time, update_time, created_at,order_serial,msgId) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        pool.query(sql, ["来伊份", orderId, phone, null,extCode, quantity,rightsId,price, productId, skuId, skuCode, skuName, userId, 0, '', '', 0, 0, createTime,new Date(),new Date(),order_serial,msgId],(err, result) => {
            if (err) console.log(err);
            // let sql2 ="INSERT INTO  t_order_sms(t_order_id, phone, type, status, result, created_at,updated_at) VALUES (?,?,?,?,?,?,?)";
            // pool.query(sql2, [orderId, phone,1,200,200,new Date(),new Date()],(err2, result2) => {
                // if (err2) console.log(err2);
            // });
        });
      }

    }else{
      console.log("插入状态不为0的订单");
      //已发货、已领取
      //先处理地址
      const components =utils.extractAddressComponents(detailAddress);
      let province=components.province;
      let city=components.city;
      let county=components.district;
      let address=components.details;
      let name=bodyJson.recipient;
      let totalData=await utils.getSumByCorn({name:"his_address",where:`WHERE order_id="${orderId}"`});
      //判断订单是否存在
      if(orderRes.total==0){
        //订单不存在
        let sql4 = `INSERT INTO  his_address(order_id,origin_address) VALUES (?,?) `;
        pool.query(sql4, [orderId,detailAddress],(err4, result4) => {
            if (err4) console.log(err4);
            let sql3 = `INSERT INTO  t_user_consignee(province,city, phone, county,detail_address,name,created_at,updated_at,t_user_id) VALUES (?,?,?,?,?,?,?,?,?) `;
            pool.query(sql3, [province,city,recipientNumber,county,address,name,new Date(),new Date(),userId],(err3, result3) => {
                if (err3) console.log(err3);
                let sql ="INSERT INTO  t_order(source, order_id, phone, receive_time, ext_code, quantity,rights_id, price, product_id, sku_id, sku_code, sku_name, user_id, user_consignee_id,user_consignee_address ,user_consignee_phone,user_consignee_name, express_company, tracking_number, state, sms_num, create_time, update_time, created_at,order_serial) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                pool.query(sql, ["来伊份", orderId, phone, receiveTime,extCode, quantity,rightsId,price, productId, skuId, skuCode, skuName, userId, result3.insertId,detailAddress,recipientNumber,name, '', trackingNumber?trackingNumber:'', state, 0, createTime,new Date(),new Date(),order_serial],(err, result) => {
                  if (err) console.log(err);
                    // let sql2 ="INSERT INTO  t_order_sms(t_order_id, phone, type, status, result, created_at,updated_at) VALUES (?,?,?,?,?,?,?)";
                    // pool.query(sql2, [orderId, phone,1,200,200,new Date(),new Date()],(err2, result2) => {
                    //     if (err2) if (err2) console.log(err2);
                    // });
                });
            });
        });
      }else{
        if(totalData.total==0){
          let sql = `INSERT INTO  his_address(order_id,origin_address) VALUES (?,?) `;
          pool.query(sql, [orderId,detailAddress],(err, result) => {
            if (err) console.log(err);
              let sql3 = `INSERT INTO  t_user_consignee(province,city, phone, county,detail_address,name,created_at,updated_at,t_user_id) VALUES (?,?,?,?,?,?,?,?,?) `;
              pool.query(sql3, [province,city,recipientNumber,county,address,name,new Date(),new Date(),userId],(err3, result3) => {
                if (err3) console.log(err3);
                  let sql2 = `update t_order  set receive_time=?,user_consignee_id=?,user_consignee_address=?,user_consignee_phone=?,user_consignee_name=?,express_company=?,tracking_number=?,state=?,update_time=?   WHERE order_id ="${orderId}" `;
                  pool.query(sql2, [receiveTime,result3.insertId,detailAddress,recipientNumber,name,'',trackingNumber?trackingNumber:'',state,new Date()],(err2, result2) => {
                      if (err2) console.log(err2);
                  });
              });
          });
        }else{
          //地址表有记录，
        //   let consigneeId=await utils.getConsigneeIdByCorn({province,city,recipientNumber,county,address,name});
        //   console.log("consigneeId="+consigneeId);
            if(trackingNumber){
                let sql2 = `update t_order  set receive_time=?,user_consignee_id=?,user_consignee_address=?,user_consignee_phone=?,user_consignee_name=?,tracking_number=?,state=?,update_time=?   WHERE order_id ="${orderId}" `;
                pool.query(sql2, [receiveTime,0,detailAddress,recipientNumber,name,trackingNumber?trackingNumber:'',state,new Date()],(err2, result2) => {
                    if (err2) console.log(err2);
                });
            }
        }
      }
    }
  }
});

//创建一个每天晚上十点
cron.schedule('0 22 * * *',async () => {
    let datetime=utils.getDateTime();
    console.log(datetime+':每晚十点定时任务执行中...');
    var page=1;
    var bigData=[];
    let date=utils.getDaysAgo(90);
    while (true) {
      let result =await utils.getLaiyifenOrder(date,page);
      bigData= bigData.concat(result);
      // 检查条件是否满足，如果满足则退出循环
      if (result.length<50) {
        console.log('条件满足，退出循环');
        break;
      }
      page++;
    }  
    console.log(bigData.length);
    console.log(datetime+':数据...');
    console.log(bigData);
    console.log(datetime+':数据...');
    for(let i=0;i<bigData.length;i++){
      console.log("次数=",i);
      let  bodyJson = bigData[i];
      let phone=bodyJson.serverNum;
      console.log(999,phone);
      //查询用户
      let userId=await utils.getTUserIdByCorn({phone:phone});
  
      //查询产品Id
      let skuId=bodyJson.skuId;
      let skuCode=bodyJson.skuCode;
      let skuName=bodyJson.skuName;
        if(skuCode=='lyf01'){
            skuName="来伊份零食畅享盲盒月卡";
        }
        if(skuCode=='lyf02'){
            skuName="来伊份零食精选盲盒月卡";
        }
        if(skuCode=='lyf03'){
            skuName="来伊份零食十全大礼包";
        }
      let productId=await utils.getProductIdByCorn({skuId,skuCode,skuName});
  
  
      let orderId=bodyJson.orderItemId;
      let extCode=bodyJson.extCode;
      let quantity=bodyJson.quantity;
      let rightsId=bodyJson.rightsId;
      let price=bodyJson.price;
      let detailAddress=bodyJson.recipientAddress;
  
      let recipientNumber=bodyJson.recipientNumber;
      let receiveTime;
      if(bodyJson.receiveTime==null ||  bodyJson.receiveTime==''){
        receiveTime=null;
      }else{
        receiveTime= dayjs(Number(bodyJson.receiveTime)).format('YYYY-MM-DD HH:mm:ss');
      }
      let trackingNumber=bodyJson.trackingNumber;
      let state=0;
      if(recipientNumber!='' && recipientNumber!=null){
        trackingNumber!=''  && trackingNumber!=null?state=2:state=1
      }
      let createTime = dayjs(Number(bodyJson.createTime)).format('YYYY-MM-DD HH:mm:ss')
      console.log(999,createTime);
      let order_serial='';
      for (let i = 0; i < 12; i++) {
        order_serial += Math.floor(Math.random() * 10); // 生成0到9之间的随机数
      }
      let orderRes=await utils.getSumByCorn({name:"t_order",where:`WHERE order_id="${orderId}"`});
      console.log("本次要插入的历史订单");
      console.log(bodyJson);
      console.log("本次要插入的历史订单");
      if(state==0){
        console.log("插入状态为0的订单");
        //未领取
        //插入订单-判断订单是否存在，不存在就新增，存在不做操作
        if(orderRes.total==0){
          let createTimestamp = new Date(createTime).getTime();
          console.log(111,createTimestamp);
          let targetTimestamp = new Date(CHANGE_TIME).getTime();
          console.log(222,targetTimestamp);
          let timeDiff = createTimestamp - targetTimestamp;
          console.log(333,timeDiff);
          let msgId='';
          let sql ="INSERT INTO  t_order(source, order_id, phone, receive_time, ext_code, quantity,rights_id, price, product_id, sku_id, sku_code, sku_name, user_id, user_consignee_id, express_company, tracking_number, state, sms_num, create_time, update_time, created_at,order_serial,msgId) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
          pool.query(sql, ["来伊份", orderId, phone, null,extCode, quantity,rightsId,price, productId, skuId, skuCode, skuName, userId, 0, '', '', 0, 0, createTime,new Date(),new Date(),order_serial,msgId],(err, result) => {
              if (err) console.log(err);
              // let sql2 ="INSERT INTO  t_order_sms(t_order_id, phone, type, status, result, created_at,updated_at) VALUES (?,?,?,?,?,?,?)";
              // pool.query(sql2, [orderId, phone,1,200,200,new Date(),new Date()],(err2, result2) => {
                  // if (err2) console.log(err2);
              // });
          });
        }
  
      }else{
        console.log("插入状态不为0的订单");
        //已发货、已领取
        //先处理地址
        const components =utils.extractAddressComponents(detailAddress);
        console.log(components);
        let province=components.province;
        let city=components.city;
        let county=components.district;
        let address=components.details;
        let name=bodyJson.recipient;
        let totalData=await utils.getSumByCorn({name:"his_address",where:`WHERE order_id="${orderId}"`});
        console.log(123,totalData);
        //判断订单是否存在
        if(orderRes.total==0){
          //订单不存在
          console.log("插入状态不为0的新订单");
          let sql4 = `INSERT INTO  his_address(order_id,origin_address) VALUES (?,?) `;
          pool.query(sql4, [orderId,detailAddress],(err4, result4) => {
              if (err4) console.log(err4);
              console.log(333,result4);
              let sql3 = `INSERT INTO  t_user_consignee(province,city, phone, county,detail_address,name,created_at,updated_at,t_user_id) VALUES (?,?,?,?,?,?,?,?,?) `;
              pool.query(sql3, [province,city,recipientNumber,county,address,name,new Date(),new Date(),userId],(err3, result3) => {
                  if (err3) console.log(err3);
                  console.log(12345678);
                  console.log(90,result3.insertId);
                  let sql ="INSERT INTO  t_order(source, order_id, phone, receive_time, ext_code, quantity,rights_id, price, product_id, sku_id, sku_code, sku_name, user_id, user_consignee_id,user_consignee_address ,user_consignee_phone,user_consignee_name, express_company, tracking_number, state, sms_num, create_time, update_time, created_at,order_serial) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                  pool.query(sql, ["来伊份", orderId, phone, receiveTime,extCode, quantity,rightsId,price, productId, skuId, skuCode, skuName, userId, result3.insertId,detailAddress,recipientNumber,name, '', trackingNumber?trackingNumber:'', state, 0, createTime,new Date(),new Date(),order_serial],(err, result) => {
                    if (err) console.log(err);
                      console.log(000,result);
                      // let sql2 ="INSERT INTO  t_order_sms(t_order_id, phone, type, status, result, created_at,updated_at) VALUES (?,?,?,?,?,?,?)";
                      // pool.query(sql2, [orderId, phone,1,200,200,new Date(),new Date()],(err2, result2) => {
                      //     if (err2) if (err2) console.log(err2);
                      // });
                  });
              });
          });
        }else{
          if(totalData.total==0){
            console.log("更新状态不为0的存在的订单，但地址不存在");
            let sql = `INSERT INTO  his_address(order_id,origin_address) VALUES (?,?) `;
            pool.query(sql, [orderId,detailAddress],(err, result) => {
              if (err) console.log(err);
                let sql3 = `INSERT INTO  t_user_consignee(province,city, phone, county,detail_address,name,created_at,updated_at,t_user_id) VALUES (?,?,?,?,?,?,?,?,?) `;
                pool.query(sql3, [province,city,recipientNumber,county,address,name,new Date(),new Date(),userId],(err3, result3) => {
                  if (err3) console.log(err3);
                    console.log(3333,result3);
                    let sql2 = `update t_order  set receive_time=?,user_consignee_id=?,user_consignee_address=?,user_consignee_phone=?,user_consignee_name=?,express_company=?,tracking_number=?,state=?,update_time=?   WHERE order_id ="${orderId}" `;
                    pool.query(sql2, [receiveTime,result3.insertId,detailAddress,recipientNumber,name,'',trackingNumber?trackingNumber:'',state,new Date()],(err2, result2) => {
                        if (err2) console.log(err2);
                    });
                });
            });
          }else{
            //地址表有记录，
            console.log("更新状态不为0的存在的订单，而且地址存在");
          //   let consigneeId=await utils.getConsigneeIdByCorn({province,city,recipientNumber,county,address,name});
          //   console.log("consigneeId="+consigneeId);
            if(trackingNumber){
                let sql2 = `update t_order  set receive_time=?,user_consignee_id=?,user_consignee_address=?,user_consignee_phone=?,user_consignee_name=?,tracking_number=?,state=?,update_time=?   WHERE order_id ="${orderId}" `;
                pool.query(sql2, [receiveTime,0,detailAddress,recipientNumber,name,trackingNumber?trackingNumber:'',state,new Date()],(err2, result2) => {
                if (err2) console.log(err2);
                    console.log(22,result2);
                });
            }
          }
        }
      }
    }
});



module.exports = router;